package com.ctsi.ssdc.mapper;

import com.ctsi.ssdc.entity.Excel08;
import com.ctsi.ssdc.entity.Excel08VO.Excel08VO;
import com.ctsi.ssdc.entity.Excel08VO.MonthProductVo;
import com.ctsi.ssdc.entity.IndustrialEconomicIndicators;
import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.springframework.data.domain.Pageable;

import java.util.Date;
import java.util.List;
import org.apache.ibatis.annotations.Mapper;
/**
 * 8基础表工业分行业(产值能耗)(Excel08)表数据库访问层
 *
 * @author makejava
 * @since 2023-03-14 18:30:18
 */
 @Mapper
public interface Excel08Mapper {

    /**
     * 通过ID查询单条数据
     *
     * @param id 主键
     * @return 实例对象
     */
    Excel08 queryById(Long id);

    /**
     * 查询指定行数据
     *
     * @param excel08 查询条件
     * @param pageable         分页对象
     * @return 对象列表
     */
    List<Excel08> queryAllByLimit(@Param("queryVO") Excel08 excel08, @Param("pageable") Pageable pageable);

    /**
     * 统计总行数
     *
     * @param excel08 查询条件
     * @return 总行数
     */
    long count(Excel08 excel08);

    /**
     * 新增数据
     *
     * @param excel08 实例对象
     * @return 影响行数
     */
    int insert(Excel08 excel08);

    /**
     * 批量新增数据（MyBatis原生foreach方法）
     *
     * @param entities List<Excel08> 实例对象列表
     * @return 影响行数
     */
    int insertBatch(@Param("list") List<Excel08> entities);

    /**
     * 批量新增或按主键更新数据（MyBatis原生foreach方法）
     *
     * @param entities List<Excel08> 实例对象列表
     * @return 影响行数
     * @throws org.springframework.jdbc.BadSqlGrammarException 入参是空List的时候会抛SQL语句错误的异常，请自行校验入参
     */
    int insertOrUpdateBatch(@Param("list") List<Excel08> entities);

    /**
     * 修改数据
     *
     * @param excel08 实例对象
     * @return 影响行数
     */
    int update(Excel08 excel08);

    /**
     * 通过主键删除数据
     *
     * @param id 主键
     * @return 影响行数
     */
    int deleteById(Long id);

    /**
     * 查询集团名称
     * @param year
     * @param month
     * @return
     */
    @Select("SELECT `group` FROM excel_08\n" +
            "WHERE `group` IS NOT NULL\n" +
            "AND YEAR(statisticalTime) = #{year}\n" +
            "AND MONTH(statisticalTime) = #{month}\n" +
            "GROUP BY `group`")
    List<String> getCompanyName(@Param("year") String year,@Param("month") String month);

    /**
     * 查询法人名称
     * @param year
     * @param month
     * @return
     */
    @Select("SELECT person FROM excel_08\n" +
            "WHERE person IS NOT NULL\n" +
            "AND YEAR(statisticalTime) = #{year}\n" +
            "AND MONTH(statisticalTime) = #{month}\n" +
            "GROUP BY person")
    List<String> getPersonName(@Param("year") String year,@Param("month") String month);


    /**
     * 根据法人或集团信息获取一个月的公司数据
     * @param year
     * @param month
     * @param name
     * @return
     */
    @Select("SELECT id,`name`,ROUND(gycz_currentMonth) gyczCurrentmonth,ROUND(gycz_previousMonth) gyczPreviousmonth,ROUND(gycz_monthOnMonth1,2) gyczMonthonmonth1,ROUND(gycz_currentAccumulate) gyczCurrentaccumulate,ROUND(gycz_previousAccumulate) gyczPreviousaccumulate,ROUND(gycz_monthOnMonth2,2) gyczMonthonmonth2,`group`,statisticalTime FROM excel_08 " +
            "WHERE `group` = #{name} " +
            "AND YEAR(statisticalTime) = #{year} " +
            "AND MONTH(statisticalTime) = #{month}")
    List<Excel08> getInfo(@Param("year") String year,@Param("month") String month,@Param("name") String name);

    /**
     * 根据法人或集团信息获取一个月的公司数据
     * @param year
     * @param month
     * @param name
     * @return
     */
    @Select("SELECT id,`name`,ROUND(gycz_currentMonth) gyczCurrentmonth,ROUND(gycz_previousMonth) gyczPreviousmonth,ROUND(gycz_monthOnMonth1,2) gyczMonthonmonth1,ROUND(gycz_currentAccumulate) gyczCurrentaccumulate,ROUND(gycz_previousAccumulate) gyczPreviousaccumulate,ROUND(gycz_monthOnMonth2,2) gyczMonthonmonth2,`person` as `group`,statisticalTime FROM excel_08 " +
            "WHERE `person` = #{name} " +
            "AND YEAR(statisticalTime) = #{year} " +
            "AND MONTH(statisticalTime) = #{month}")
    List<Excel08> getInfoPerson(@Param("year") String year,@Param("month") String month,@Param("name") String name);


    /**
     * 获取一个月公司按照行业分类的数据
     * 规模以上工业分行业产值
     * @param year
     * @param month
     * @return
     */
    @Select("SELECT sectorName as `name`, " +
            "ROUND(SUM(gycz_currentMonth)) as gyczCurrentmonth, " +
            "ROUND(SUM(gycz_previousMonth)) as gyczPreviousmonth, " +
            "ROUND(gycz_monthOnMonth1,1) as gyczMonthonmonth1, " +
            "ROUND(SUM(gycz_currentAccumulate)) as gyczCurrentaccumulate, " +
            "ROUND(SUM(gycz_previousAccumulate)) as gyczPreviousaccumulate, " +
            "ROUND(gycz_monthOnMonth2,1) as gyczMonthonmonth2, " +
            "statisticalTime " +
            "FROM excel_08 " +
            "WHERE YEAR(statisticalTime) = #{year} AND MONTH(statisticalTime) = #{month} " +
            "GROUP BY sectorName " +
            "ORDER BY gyczCurrentaccumulate DESC")
    List<Excel08> getInfoSectorName(@Param("year") String year,@Param("month") String month);

    @Select("SELECT `group` as `name`,ROUND(SUM(gycz_currentAccumulate)) as accumulate,MONTH(statisticalTime) as month " +
            "FROM excel_08 " +
            "WHERE `group` = #{name} " +
            "AND YEAR(statisticalTime) = #{year} " +
            "GROUP BY statisticalTime")
    List<MonthProductVo> getInfoYear(@Param("year") String year, @Param("name") String name);

    /**
     * 获取某公司一年内每个月信息
     * @param year
     * @param name
     * @return
     */
    @Select("SELECT `name` as `name`,ROUND(SUM(gycz_currentAccumulate)) as accumulate,MONTH(statisticalTime) as month " +
            "FROM excel_08 " +
            "WHERE `name` = #{name} " +
            "AND YEAR(statisticalTime) = #{year} " +
            "GROUP BY statisticalTime")
    List<MonthProductVo> getInfoYearCompany(@Param("year") String year, @Param("name") String name);

    /**
     * 查询重点企业
     * @param year
     * @param month
     * @return
     */
    @Select("SELECT id,`name`, gycz_currentMonth, gycz_previousMonth, \n" +
            "gycz_currentAccumulate, gycz_previousAccumulate, \n" +
            "gycz_monthOnMonth1, gycz_monthOnMonth2,\n" +
            "`group`\n" +
            "FROM excel_08\n" +
            "WHERE zdbj = 1\n" +
            "AND YEAR(statisticalTime) = #{year}\n" +
            "AND MONTH(statisticalTime) = #{month}")
    List<Excel08VO> getKeyInfo(@Param("year") String year, @Param("month") String month);

    /**
     * 获取规上工业总产值当月 + 累计 + 同比
     * @param year
     * @param month
     * @return
     */
    @Select("SELECT ROUND(SUM(gycz_currentMonth)/10000,2) as thisMonth,\n" +
            "ROUND(SUM(gycz_currentAccumulate)/10000,2) as accumulate, \n" +
            "ROUND((SUM(gycz_currentAccumulate)/SUM(gycz_previousAccumulate) - 1)*100,1) as monthOnMonth\n" +
            "FROM excel_08\n" +
            "WHERE YEAR(statisticalTime) = #{year}\n" +
            "AND MONTH(statisticalTime) = #{month}")
    IndustrialEconomicIndicators getIndustryOutputInfo(@Param("year") String year,@Param("month") String month);

    /**
     * 综合能源消费量（吨标准煤）当月(需要计算) + 累计 + 累计同比
     * @param year
     * @param month
     * @return
     */
    @Select("SELECT ROUND(SUM(nyxfl_currentAccumulate)) as accumulate,\n" +
            "ROUND((SUM(nyxfl_currentAccumulate)/SUM(nyxfl_previousAccumulate) - 1)*100,1) as monthOnMonth\n" +
            "FROM excel_08\n" +
            "WHERE YEAR(statisticalTime) = #{year}\n" +
            "AND MONTH(statisticalTime) = #{month}")
    IndustrialEconomicIndicators getEnergyConsumptionInfo(@Param("year") String year,@Param("month") String month);

    /**
     * 规上工业用电量（万千瓦时）当月 + 累计 + 同比
     * @param year
     * @param month
     * @return
     */
    @Select("SELECT ROUND(SUM(gyydl_currentAccumulate)) as accumulate, \n" +
            "ROUND((SUM(gyydl_currentAccumulate)/SUM(gyydl_previousAccumulate) - 1)*100,1) as monthOnMonth\n" +
            "FROM excel_08\n" +
            "WHERE YEAR(statisticalTime) = #{year}\n" +
            "AND MONTH(statisticalTime) = #{month}")
    IndustrialEconomicIndicators getIndustrialElectricityInfo(@Param("year") String year,@Param("month") String month);

    /**
     * 获取万元产值能耗（吨标准煤/万元）当月 + 累计 + 同比
     * @param year
     * @param month
     * @return
     */
    @Select("SELECT ROUND(SUM(nyxfl_currentAccumulate)/SUM(gycz_currentAccumulate),4) as accumulate,\n" +
            "ROUND(\n" +
            "((SUM(nyxfl_currentAccumulate)/SUM(gycz_currentAccumulate))/\n" +
            "(SUM(nyxfl_previousAccumulate)/SUM(gycz_previousAccumulate))-1)\n" +
            "*100,1) as monthOnMonth\n" +
            "FROM excel_08\n" +
            "WHERE YEAR(statisticalTime) = #{year}\n" +
            "AND MONTH(statisticalTime) = #{month}")
    IndustrialEconomicIndicators getEnergyConsumption1wInfo(@Param("year") String year,@Param("month") String month);

    /**
     * 获取高新技术产业产值当月 + 累计 + 同比
     * @param year
     * @param month
     * @return
     */
    IndustrialEconomicIndicators getHighTechInfo(@Param("year") String year,@Param("month") String month);

    IndustrialEconomicIndicators getHighTechProportionInfo(@Param("year") String year, @Param("month") String month);

    @Select("SELECT person as `name`,ROUND(SUM(gycz_currentAccumulate)) as accumulate,MONTH(statisticalTime) as month " +
            "FROM excel_08 " +
            "WHERE person = #{name} " +
            "AND YEAR(statisticalTime) = #{year} " +
            "GROUP BY statisticalTime")
    List<MonthProductVo> getInfoYearPerson(@Param("year") String year, @Param("name") String name);

    /**
     * 根据日期删除
     * @param date
     * @return 是否成功
     */
    @Delete("delete from excel_08 where statisticalTime = #{date};")
    boolean removeByDate(@Param("date") Date date);
}

